package com.mg.util;  
  
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.Region;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.jfinal.plugin.activerecord.Config;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.DbKit;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.druid.DruidPlugin;

  
public class ExcelUtil {  
      
    public static final String HEADERINFO="headInfo";  
    public static final String DATAINFON="dataInfo";  
      
    /** 
     *  
     * @Title: getWeebWork 
     * @Description: TODO(根据传入的文件名获取工作簿对象(Workbook)) 
     * @param filename 
     * @return 
     * @throws IOException  
     */  
    public static Workbook getWeebWork(String filename) throws IOException{  
        Workbook workbook=null;  
        if(null!=filename){  
            String fileType=filename.substring(filename.lastIndexOf("."),filename.length());  
            FileInputStream fileStream = new FileInputStream(new File(filename));  
            if(".xls".equals(fileType.trim().toLowerCase())){  
                workbook = new HSSFWorkbook(fileStream);// 创建 Excel 2003 工作簿对象  
            }else if(".xlsx".equals(fileType.trim().toLowerCase())){  
                workbook = new XSSFWorkbook(fileStream);//创建 Excel 2007 工作簿对象  
            }  
        }  
        return workbook;  
    }  
    /** 
     *  
     * @Title: writeExcel 
     * @Description: TODO(导出Excel表) 
     * @param pathname:导出Excel表的文件路径 
     * @param map：封装需要导出的数据(HEADERINFO封装表头信息，DATAINFON：封装要导出的数据信息,此处需要使用TreeMap) 
     * 例如： map.put(ExcelUtil.HEADERINFO,List<String> headList); 
     *       map.put(ExcelUtil.DATAINFON,List<TreeMap<String,Object>>  dataList); 
     * @param wb 
     * @throws IOException 
     */  
    public static void writeExcel(String pathname,Map<String,Object> map,Workbook wb) throws IOException{  
        if(null!=map && null!=pathname){  
            List<Object> headList = (List<Object>) map.get(ExcelUtil.HEADERINFO);  
            List<Record> dataList = (List<Record>) map.get(ExcelUtil.DATAINFON);  
            CellStyle style = getCellStyle(wb);  
            Sheet sheet = wb.createSheet("sheet1");
            sheet.setColumnWidth(0, 15*256);
            sheet.setColumnWidth(1, 20*256);
            sheet.setColumnWidth(2, 15*256);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
            /** 
             * 设置Excel表的第一行即表头 
             */  
            Row row =sheet.createRow(0);  
            Cell headCell2 = row.createCell(0);
            headCell2.setCellType(Cell.CELL_TYPE_STRING);
            headCell2.setCellValue("表一");
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            headCell2.setCellStyle(style);//设置表头样式  
            Row row2=sheet.createRow(1);
            for(int i=0;i<headList.size();i++){  
                Cell headCell = row2.createCell(i);  
                headCell.setCellType(Cell.CELL_TYPE_STRING);  
                headCell.setCellStyle(style);//设置表头样式  
                headCell.setCellValue(String.valueOf(headList.get(i)));  
            }  
            
            for (int i = 0; i < dataList.size(); i++) {  
                Row rowdata = sheet.createRow(i+2);//创建数据行  
                Record r =dataList.get(i);  
                Object[] vals = r.getColumnValues();
                for(int j=0;j<vals.length;j++){
                    Cell celldata = rowdata.createCell(j);  
                    celldata.setCellType(Cell.CELL_TYPE_STRING);  
                    celldata.setCellValue(vals[j]+""); 
                    CellStyle cs = wb.createCellStyle();
                    cs.setWrapText(true);
                    celldata.setCellStyle(cs);
                }
            }  
            File file = new File(pathname);  
            OutputStream os = new FileOutputStream(file);  
            os.flush();  
            wb.write(os);  
            os.close();  
        }  
    }  
    /** 
     *  
     * @Title: getCellStyle 
     * @Description: TODO（设置表头样式） 
     * @param wb 
     * @return 
     */  
    public static CellStyle getCellStyle(Workbook wb){  
        CellStyle style = wb.createCellStyle();  
        Font font = wb.createFont();  
        font.setFontName("宋体");  
        font.setFontHeightInPoints((short)12);//设置字体大小  
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗  
        style.setFillForegroundColor(HSSFColor.LIME.index);// 设置背景色  
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
        style.setAlignment(HSSFCellStyle.SOLID_FOREGROUND);//让单元格居中  
        //style.setWrapText(true);//设置自动换行  
        style.setFont(font);  
        return style;  
    }  
      
    /** 
     *  
     * @Title: readerExcelDemo 
     * @Description: TODO(读取Excel表中的数据) 
     * @throws IOException 
     */  
    public static void readerExcelDemo() throws IOException{  
        /** 
         * 读取Excel表中的所有数据 
         */  
        Workbook workbook=getWeebWork("C:/1.xls");  
        System.out.println("总表页数为："+workbook.getNumberOfSheets());//获取表页数  
        Sheet sheet =workbook.getSheetAt(0);  
        int rownum=sheet.getLastRowNum();//获取总行数  
        for (int i = 0; i < rownum; i++) {  
            Row row =sheet.getRow(i);  
            Cell orderno = row.getCell(2);//获取指定单元格中的数据  
            System.out.println(orderno.getCellType());  
            short cellnum=row.getLastCellNum(); //获取单元格的总列数  
            for(int j=row.getFirstCellNum();j< row.getLastCellNum();j++){  
                Cell celldata = row.getCell(j);  
                System.out.print(celldata+"\t");  
            }  
            System.out.println();  
        }  
          
        /** 
         * 读取指定位置的单元格 
         */  
        Row row1 = sheet.getRow(1);  
        Cell cell1 = row1.getCell(2);  
        System.out.print("(1,2)位置单元格的值为："+cell1);  
        //BigDecimal big = new BigDecimal(cell1.getNumericCellValue());//将科学计数法表示的数据转化为String类型  
        //System.out.print("\t"+String.valueOf(big));  
          
    }  
      
    public static void main(String[] args) throws IOException {  
                 
        Workbook wb = new XSSFWorkbook();  
        Map<String,Object> map = new HashMap<String,Object>();  
        //连接数据库
        String url = "jdbc:mysql://127.0.0.1:3306/from?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull";
        String username = "root";
        String password = "root";
        String driverClass = "com.mysql.jdbc.Driver";
        DruidPlugin ds = new DruidPlugin(url, username, password, driverClass);
        ds.start();
        DbKit.addConfig(new Config("ds", ds.getDataSource()));
        List<String> headList = new ArrayList<String>();//表头数据  
        String sql = "select code '编码',name '名称',parent_code '父级编码' from pub_region";
        Record r = Db.use("ds").findFirst(sql);
        for(String colName : r.getColumnNames()){
        	headList.add(colName);
        }
        List<Record> list = Db.use("ds").find(sql);
        ds.stop();
        map.put(ExcelUtil.HEADERINFO, headList);  
        map.put(ExcelUtil.DATAINFON, list);  
        writeExcel("E:/test.xlsx", map, wb);  
    }  
}  